from clickhouse_driver import Client
import pandas as pd
import threading
import time
import re

import constant
import lcs
import validation


def createTable(tableName, columns):
    return "CREATE TABLE " + tableName + columns


def deleteTable(tableName):
    return "DROP TABLE IF EXISTS " + tableName


ExpertColumns = (
    " ("
    " `uuid` String,"
    " `name` Nullable(String),"
    " `organization` Nullable(String),"
    " `Second_organization` Nullable(String),"
    " `email` Nullable(String),"
    " `reprintauthor` Nullable(String),"
    " `altname` Nullable(String),"
    " `country` Nullable(String),"
    " `firstauthor` Nullable(String),"
    " `organizationdept` Nullable(String),"
    " `keywords` Nullable(String),"
    " `subjects` Nullable(String),"
    " `journals` Nullable(String),"
    " `source_email` Nullable(String)"
    " )"
    " ENGINE = MergeTree"
    " ORDER BY uuid"
    " SETTINGS index_granularity = 8192"
)
RelationshipColumns = (
    " ("
    " `uuid` String,"
    " `guid` String"
    " )"
    " ENGINE = MergeTree"
    " ORDER BY uuid"
    " SETTINGS index_granularity = 8192"
)
RepeatNameColumns = (
    " ("
    " `name` String,"
    " `num`  Int32"
    " )"
    " ENGINE = MergeTree"
    " ORDER BY name"
    " SETTINGS index_granularity = 8192"
)
RepeatNameUuidColumns = (
    " ("
    " `uuid` String,"
    " `ruuid` String"
    " )"
    " ENGINE = MergeTree"
    " ORDER BY uuid"
    " SETTINGS index_granularity = 8192"
)
RepeatNameUuid1Columns = (
    " ("
    " `name` String,"
    " `uuid` String"
    " )"
    " ENGINE = MergeTree"
    " ORDER BY uuid"
    " SETTINGS index_granularity = 8192"
)
ExpertQueryColumns = (
    " uuid,name,organization,Second_organization,email,reprintauthor,altname,country,firstauthor,organizationdept,"
    " keywords,subjects,journals,source_email "
)

China = constant.China
OutPut = constant.OutPut
Expert = OutPut + "_Expert"
ExpertDelete = deleteTable(Expert)
ExpertCreate = createTable(Expert, ExpertColumns)
ExpertTmp = Expert + "_Tmp"
ExpertTmpDelete = deleteTable(ExpertTmp)
ExpertTmp1 = ExpertTmp + "1"
ExpertTmp1Delete = deleteTable(ExpertTmp1)
ExpertTmp2 = ExpertTmp + "2"
ExpertTmp2Delete = deleteTable(ExpertTmp2)
ExpertTmp2Create = createTable(ExpertTmp2, ExpertColumns)
ExpertTmp3 = ExpertTmp + "3"
ExpertTmp3Delete = deleteTable(ExpertTmp3)
ExpertTmp3Create = createTable(ExpertTmp3, ExpertColumns)
Relationship = OutPut + "_Experts_Relationship"
RelationshipDelete = deleteTable(Relationship)
RelationshipCreate = createTable(Relationship, RelationshipColumns)
RelationshipTmp = Relationship + "_Tmp"
RelationshipTmpDelete = deleteTable(RelationshipTmp)
RelationshipTmp1 = RelationshipTmp + "1"
RelationshipTmp1Delete = deleteTable(RelationshipTmp1)
RelationshipTmp2 = RelationshipTmp + "2"
RelationshipTmp2Delete = deleteTable(RelationshipTmp2)
RelationshipTmp2Create = createTable(RelationshipTmp2, RelationshipColumns)
RelationshipTmp3 = RelationshipTmp + "3"
RelationshipTmp3Delete = deleteTable(RelationshipTmp3)
RelationshipTmp3Create = createTable(RelationshipTmp3, RelationshipColumns)
RepeatName = Expert + "_Repeat_Name"
RepeatNameDelete = deleteTable(RepeatName)
RepeatNameCreate = createTable(RepeatName, RepeatNameColumns)
RepeatNameUuid = RepeatName + "_Uuid"
RepeatNameUuidDelete = deleteTable(RepeatNameUuid)
RepeatNameUuidCreate = createTable(RepeatNameUuid, RepeatNameUuidColumns)
RepeatNameUuid1 = RepeatName + "_Uuid1"
RepeatNameUuid1Delete = deleteTable(RepeatNameUuid1)
RepeatNameUuid1Create = createTable(RepeatNameUuid1, RepeatNameUuid1Columns)
RepeatOrg = Expert + "_Repeat_Org"
RepeatOrgDelete = deleteTable(RepeatOrg)
RepeatOrgUuid = RepeatOrg + "_Uuid"
RepeatOrgUuidDelete = deleteTable(RepeatOrgUuid)
RepeatOrgExecute = Expert + "_Repeat_Org_Execute"
RepeatOrgExecuteDelete = deleteTable(RepeatOrgExecute)


def heartbeatQuery(client):
    """
    心跳查询，防止程序执行时间过程，数据库链接断开
    """
    client.execute("select 1")


def escapeCharacter(str):
    """
    数据库sql字符串转义
    \n ' 转义为  ''
    \n { 转义为  {{
    \n } 转义为  }}
    :param str:
    :return:
    """

    return str.replace("'", "''").replace("{", "{{").replace("}", "}}")


def getDbStr(strParam):
    strParam = str(strParam)
    max = len(strParam)
    i = 0
    while True:
        strParam = strParam.strip()
        if strParam.startswith("'"):
            strParam = strParam[1:]
        if strParam.endswith("'"):
            strParam = strParam[:len(strParam) - 1]
        if strParam.endswith("\\"):
            strParam = strParam[:len(strParam) - 1]
        if not strParam.startswith("'") and not strParam.endswith("'") and not strParam.endswith("\\"):
            # 收尾的单引号都删除后，返回
            break
        i += 1
        if i > max:
            # 防止死循环
            break
    return "'" + strParam + "'"


def arrUnique(arr):
    return list(pd.unique(arr))


def strUniqueAndsplitBySemicolon(str):
    """
    去重，分号隔开
    """
    arr = []
    if str is not None and len(str.strip()) > 0:
        arr1 = str.split(";")
        for item1 in arr1:
            if len(item1.strip()) > 0:
                arr2 = item1.strip().split(",")
                for item2 in arr2:
                    if len(item2.strip()) > 0:
                        arr.append(item2.strip())
        # 去重，分号隔开
        singleArr = arrUnique(arr)
        return ';'.join(singleArr)
    else:
        return ''


def noneToEmptyStr(value):
    res = ''
    if value is not None and value != 'None':
        res = value
    return res


def noneToEmptyArr(arr):
    res = []
    if len(arr) > 0:
        for i in range(len(arr)):
            item = arr[i]
            if item is not None and item != 'None':
                res.append(str(item))
            else:
                res.append('')
    return res


def getUuidMap(uuidArr, uuidMap, uuidKey):
    newUuidArr = []
    newUuidArr.extend(uuidArr)
    for uuid in uuidArr:
        if uuid in uuidMap.keys():
            delUuidArr = uuidMap[uuid]
            newUuidArr.extend(delUuidArr)
            del uuidMap[uuid]
    uuidMap[uuidKey] = arrUnique(newUuidArr)
    return uuidMap


def insertRUuid(client, uuidMap):
    for key in uuidMap.keys():
        uuidArr = uuidMap[key]
        sql = (
                " insert into " + RepeatNameUuid + "(uuid,ruuid)VALUES"
        )
        insertNum = 0
        for i in range(len(uuidArr)):
            insertNum += 1
            itemRUuid = uuidArr[i]
            sql += "(" + getDbStr(escapeCharacter(str(key))) + "," + getDbStr(escapeCharacter(str(itemRUuid))) + "),"
            if insertNum >= 1500:
                if (sql.endswith(',')):
                    sql = sql[0:len(sql) - 1]
                if (sql.endswith(')')):
                    insertNum = 0
                    client.execute(sql)
                sql = (
                        " insert into " + RepeatNameUuid + "(uuid,ruuid)VALUES"
                )

        if insertNum > 0:
            if (sql.endswith(',')):
                sql = sql[0:len(sql) - 1]
            if (sql.endswith(')')):
                client.execute(sql)


def getGuidMapName(client, uuidMap):
    guidMap = {}
    if len(uuidMap.keys()) > 0:
        insertRUuid(client, uuidMap)
        for key in uuidMap.keys():
            sql = (
                    " select guid from " + RelationshipTmp1 +
                    " where uuid in (select ruuid from " + RepeatNameUuid +
                    " where uuid = " + getDbStr(escapeCharacter(key)) + ")"
            )
            rows = client.execute(sql)
            if rows is not None and len(rows) > 0:
                guidArr = []
                for rowsItem in rows:
                    guidArr.append(str(rowsItem[0]))
                guidArr = arrUnique(guidArr)
                guidMap[key] = guidArr
    return guidMap


def mergeRepeatExpert(expert1, expert2):
    """
        expert2 合入 expert1
    """

    organizationArr = arrUnique([noneToEmptyStr(expert1[2]), noneToEmptyStr(expert2[2])])
    secondOrganizationArr = arrUnique([noneToEmptyStr(expert1[3]), noneToEmptyStr(expert2[3])])
    # emailArr = [noneToEmptyStr(expert1[4]), noneToEmptyStr(expert2[4])]
    organizationdeptArr = arrUnique([noneToEmptyStr(expert1[9]), noneToEmptyStr(expert2[9])])
    keywordsArr = [noneToEmptyStr(expert1[10]), noneToEmptyStr(expert2[10])]
    subjectsArr = [noneToEmptyStr(expert1[11]), noneToEmptyStr(expert2[11])]
    journalsArr = [noneToEmptyStr(expert1[12]), noneToEmptyStr(expert2[12])]

    organization = strUniqueAndsplitBySemicolon(";".join(organizationArr))
    secondOrganization = strUniqueAndsplitBySemicolon(";".join(secondOrganizationArr))
    # email = strUniqueAndsplitBySemicolon(";".join(emailArr))
    email = validation.checkAndGetSingleEmail(noneToEmptyStr(expert1[4]), noneToEmptyStr(expert2[4]),
                                              noneToEmptyStr(expert1[1]), organization)
    organizationdept = strUniqueAndsplitBySemicolon(";".join(organizationdeptArr))
    keywords = strUniqueAndsplitBySemicolon(";".join(keywordsArr))
    subjects = strUniqueAndsplitBySemicolon(";".join(subjectsArr))
    journals = strUniqueAndsplitBySemicolon(";".join(journalsArr))

    expert = noneToEmptyArr(
        [expert1[0], noneToEmptyStr(expert1[1]), organization, secondOrganization,
         email, noneToEmptyStr(expert1[5]), noneToEmptyStr(expert1[6]), noneToEmptyStr(expert1[7]),
         noneToEmptyStr(expert1[8]), organizationdept, keywords, subjects, journals, noneToEmptyStr(expert1[13])])
    return expert


def insertExpertTmpName(client, expertArr, relationshipMap):
    if expertArr is None or len(expertArr) == 0 or relationshipMap is None or len(relationshipMap.keys()) == 0:
        return
    executeGuidArr = []
    sql1 = (
            " insert into " + ExpertTmp2 + "(" + ExpertQueryColumns + ")VALUES"
    )
    sql1_InsertNum = 0
    for item in expertArr:
        executeGuidArr.append(item[1])
        # 开始插表
        sql1_InsertNum += 1
        itemSql1 = (
                "(" +
                getDbStr(escapeCharacter(item[0])) + "," +
                getDbStr(escapeCharacter(item[1])) + "," +
                getDbStr(escapeCharacter(item[2])) + "," +
                getDbStr(escapeCharacter(item[3])) + "," +
                getDbStr(escapeCharacter(item[4])) + "," +
                getDbStr(escapeCharacter(item[5])) + "," +
                getDbStr(escapeCharacter(item[6])) + "," +
                getDbStr(escapeCharacter(item[7])) + "," +
                getDbStr(escapeCharacter(item[8])) + "," +
                getDbStr(escapeCharacter(item[9])) + "," +
                getDbStr(escapeCharacter(item[10])) + "," +
                getDbStr(escapeCharacter(item[11])) + "," +
                getDbStr(escapeCharacter(item[12])) + "," +
                getDbStr(escapeCharacter(item[13])) +
                "),"
        )
        sql1 = sql1 + itemSql1
        # 每 1500 条数据insert一次
        if sql1_InsertNum >= 1500:
            if (sql1.endswith(',')):
                sql1 = sql1[0:len(sql1) - 1]
            if (sql1.endswith(')')):
                client.execute(sql1)
            sql1 = (
                    " insert into " + ExpertTmp2 + "(" + ExpertQueryColumns + ")VALUES"
            )
    if sql1_InsertNum > 0:
        if (sql1.endswith(',')):
            sql1 = sql1[0:len(sql1) - 1]
        if (sql1.endswith(')')):
            client.execute(sql1)

    sql2 = (
            " insert into " + RelationshipTmp2 + "(uuid,guid)VALUES"
    )
    sql2_InsertNum = 0
    for uuidItem in relationshipMap.keys():
        guidArr = relationshipMap[uuidItem]
        for guidItem in guidArr:
            sql2_InsertNum += 1
            itemSql2 = (
                    "(" +
                    getDbStr(escapeCharacter(uuidItem)) + "," +
                    getDbStr(escapeCharacter(guidItem)) +
                    "),"
            )
            sql2 = sql2 + itemSql2
        # 每 1500 条数据insert一次
        if sql2_InsertNum >= 1500:
            if (sql2.endswith(',')):
                sql2 = sql2[0:len(sql2) - 1]
            if (sql2.endswith(')')):
                client.execute(sql2)
            sql2 = (
                    " insert into " + RelationshipTmp2 + "(uuid,guid)VALUES"
            )
    if sql2_InsertNum > 0:
        if (sql2.endswith(',')):
            sql2 = sql2[0:len(sql2) - 1]
        if (sql2.endswith(')')):
            client.execute(sql2)


def executeMergeBySameName(client, name):
    """
    合并 name 相同,organization 是子串的, Second_organization 是子串的
    """
    sql1 = (
            " select " + ExpertQueryColumns +
            " from " + ExpertTmp1 + " where replace(name,',','') = " + getDbStr(escapeCharacter(name)) +
            " group by " + ExpertQueryColumns +
            " order by length(organization) desc"
    )
    sql1_rows = client.execute(sql1)
    print(name, len(sql1_rows))
    if sql1_rows is not None and len(sql1_rows) > 0:
        columns = ExpertQueryColumns.split(",")
        df = pd.DataFrame(sql1_rows, columns=columns)

        # name，org完全相同的
        df1 = df.groupby(['organization'])
        arr1 = []
        uuidMap1 = {}
        keepMap1 = {}
        repeatIndex1 = []
        delUuidArr1 = []
        for item1 in df1:
            if len(item1[1]) == 1:
                # 唯一，无重复
                arr1.append(noneToEmptyArr(list(item1[1].values[0])))
                uuid = str(item1[1].values[0][0])
                if uuid not in uuidMap1.keys():
                    uuidArr1 = [uuid]
                    uuidMap1[uuid] = noneToEmptyArr(uuidArr1)
                else:
                    uuidArr1 = uuidMap1[uuid]
                    uuidArr1.append(uuid)
                    uuidMap1[uuid] = noneToEmptyArr(arrUnique(uuidArr1))
            else:
                # 有重复，合并，判断是不是同一个人
                item1Df = item1[1]
                for i in range(len(item1Df)):
                    uuidArr1 = []
                    uuidI = str(item1Df.values[i][0])
                    if uuidI in delUuidArr1:
                        continue
                    orgI = item1Df.values[i][2]
                    secondOrgI = item1Df.values[i][3]
                    emailI = item1Df.values[i][4]
                    key = uuidI
                    for j in range(i + 1, len(item1Df)):
                        uuidJ = str(item1Df.values[j][0])
                        if uuidJ in delUuidArr1:
                            continue
                        orgJ = item1Df.values[j][2]
                        secondOrgJ = item1Df.values[j][3]
                        emailJ = item1Df.values[j][4]
                        ok = validation.isCommonPart(orgI, orgJ)
                        s1 = validation.isCommonPart(orgI, secondOrgJ)
                        s2 = validation.isCommonPart(secondOrgI, orgJ)
                        if not (ok or s1 or s2):
                            continue
                        if emailI != "" and emailJ != "" and emailI == emailJ:
                            # 邮箱一样，直接合
                            sameExpert = True
                        else:
                            # 邮箱不一样，判断是不是一个人
                            if key not in keepMap1.keys():
                                xExpert = item1Df.values[i]
                            else:
                                xExpert = keepMap1[key]
                            yExpert = item1Df.values[j]
                            sameExpert = validation.isSameExpert_2(xExpert[10], xExpert[11], xExpert[12], xExpert[3],
                                                                   yExpert[10], yExpert[11], yExpert[12], yExpert[3])

                        if not sameExpert:
                            continue

                        # 合并作者
                        repeatIndex1.append(i)
                        repeatIndex1.append(j)
                        uuidArr1.append(uuidI)
                        uuidArr1.append(uuidJ)
                        # J 合进 I
                        delKey = uuidJ
                        delUuidArr1.append(delKey)
                        if key in uuidMap1.keys():
                            oldArr = uuidMap1[key]
                            uuidArr1.extend(oldArr)
                        uuidMap1[key] = noneToEmptyArr(arrUnique(uuidArr1))
                        if key not in keepMap1.keys():
                            xExpert = item1Df.values[i]
                        else:
                            xExpert = keepMap1[key]
                        keepExpert = mergeRepeatExpert(xExpert, item1Df.values[j])
                        if delKey in keepMap1.keys():
                            delExpert = keepMap1[delKey]
                            keepExpert = mergeRepeatExpert(keepExpert, delExpert)
                            del keepMap1[delKey]
                        orgI = keepExpert[2]
                        keepMap1[key] = keepExpert

                if len(keepMap1.keys()) > 0:
                    for key in keepMap1.keys():
                        arr1.append(keepMap1[key])
                for i in range(len(item1Df)):
                    if i not in repeatIndex1:
                        arr1.append(noneToEmptyArr(list(item1Df.values[i])))
                        key = str(item1Df.values[i][0])
                        uuidMap1[key] = [key]

        df2 = pd.DataFrame(arr1, columns=columns)

        #
        arr = []
        uuidMap = {}
        keepMap = {}
        repeatIndex = []
        delUuidArr = []
        for i in range(len(df2)):
            heartbeatQuery(client)
            uuidArr = []
            uuidI = str(df2.values[i][0])
            if uuidI in delUuidArr:
                continue
            orgI = df2.values[i][2]
            secondOrgI = df2.values[i][3]
            emailI = df2.values[i][4]
            key = uuidI
            for j in range(i + 1, len(df2)):
                uuidJ = str(df2.values[j][0])
                if uuidJ in delUuidArr:
                    continue
                orgJ = df2.values[j][2]
                secondOrgJ = df2.values[j][3]
                emailJ = df2.values[j][4]
                ok = validation.isCommonPart(orgI, orgJ)
                s1 = validation.isCommonPart(orgI, secondOrgJ)
                s2 = validation.isCommonPart(secondOrgI, orgJ)
                if not (ok or s1 or s2):
                    continue
                if emailI != "" and emailJ != "" and emailI == emailJ:
                    # 邮箱一样，直接合
                    sameExpert = True
                else:
                    # 邮箱不一样，判断是不是一个人
                    if key not in keepMap.keys():
                        xExpert = df2.values[i]
                    else:
                        xExpert = keepMap[key]
                    yExpert = df2.values[j]
                    sameExpert = validation.isSameExpert_2(xExpert[10], xExpert[11], xExpert[12], xExpert[3],
                                                           yExpert[10], yExpert[11], yExpert[12], yExpert[3])

                if not sameExpert:
                    continue

                # 合并作者
                repeatIndex.append(i)
                repeatIndex.append(j)
                uuidArr.append(uuidI)
                uuidArr.append(uuidJ)
                # J 合进 I
                key = uuidI
                if key in uuidMap1.keys():
                    oldArr = uuidMap1[key]
                    uuidArr.extend(oldArr)
                    uuidArr = arrUnique(uuidArr)
                uuidMap = getUuidMap(uuidArr, uuidMap, key)
                delKey = uuidJ
                delUuidArr.append(delKey)
                if key not in keepMap.keys():
                    xExpert = df2.values[i]
                else:
                    xExpert = keepMap[key]
                keepExpert = mergeRepeatExpert(xExpert, df2.values[j])
                if delKey in keepMap.keys():
                    delExpert = keepMap[delKey]
                    keepExpert = mergeRepeatExpert(keepExpert, delExpert)
                    del keepMap[delKey]
                orgI = keepExpert[2]
                keepMap[key] = keepExpert

        if len(keepMap.keys()) > 0:
            for key in keepMap.keys():
                arr.append(keepMap[key])
        for i in range(len(df2)):
            if i not in repeatIndex:
                arr.append(noneToEmptyArr(list(df2.values[i])))
                key = str(df2.values[i][0])
                uuidArr = [key]
                if key in uuidMap1.keys():
                    oldArr = uuidMap1[key]
                    uuidArr.extend(oldArr)
                    uuidArr = arrUnique(uuidArr)
                uuidMap = getUuidMap(uuidArr, uuidMap, key)
        guidMap = getGuidMapName(client, uuidMap)
        insertExpertTmpName(client, arr, guidMap)


def doMergeBySameNameThread(name):
    if name is not None and name != '':
        client = getClient()
        executeMergeBySameName(client, name)


def mergeBySemicolon(arr, mergeStr):
    if mergeStr is None or mergeStr == "":
        return arr
    else:
        if arr is not None:
            itemArr = mergeStr.split(";")
            if itemArr is not None:
                arr.extend(itemArr)
        return arr


def getMergeStrBySemicolon(arr):
    if arr is None or len(arr) == 0:
        return ""
    arr = arrUnique(arr)
    newArr = []
    for item in arr:
        item = item.strip()
        if len(item) > 0:
            newArr.append(item)
    return ";".join(newArr)


def getMergeStrBySemicolon2(str1, str2):
    if str1 is None:
        str1 = ""
    if str2 is None:
        str2 = ""
    a = str1 + ";" + str2
    while (True):
        if a.startswith(";"):
            a = a[1:]
        if a.endswith(";"):
            a = a[:len(a) - 1]
        if not a.startswith(";") and not a.endswith(";"):
            break
    arr = a.split(";")
    arr = arrUnique(arr)
    return ";".join(arr)


def mergeRepeatExpert2(expert1, expert2):
    """
        expert2 合入 expert1
    """

    organization = getMergeStrBySemicolon2(expert1[2], expert2[2])
    secondOrganization = getMergeStrBySemicolon2(expert1[3], expert2[3])
    email = getMergeStrBySemicolon2(expert1[4], expert2[4])
    country = getMergeStrBySemicolon2(expert1[7], expert2[7])
    organizationdept = getMergeStrBySemicolon2(expert1[9], expert2[9])
    keywords = getMergeStrBySemicolon2(expert1[10], expert2[10])
    subjects = getMergeStrBySemicolon2(expert1[11], expert2[11])
    journals = getMergeStrBySemicolon2(expert1[12], expert2[12])

    expert = noneToEmptyArr(
        [
            expert1[0], expert1[1], organization, secondOrganization, email, noneToEmptyStr(expert1[5]),
            noneToEmptyStr(expert1[6]), country, noneToEmptyStr(expert1[8]), organizationdept, keywords, subjects,
            journals, noneToEmptyStr(expert1[13])
        ]
    )
    return expert


def singleRelationship(client, name):
    sqlAllUuid = (
            " select uuid from " + ExpertTmp2 + " where replace(name,',','') = " + getDbStr(escapeCharacter(name))
    )
    sqlGuid = (
            " SELECT guid from " + RelationshipTmp2 + " where uuid in(" + sqlAllUuid + ") group by guid HAVING count() = 1 "
    )
    sqlGuid_rows = client.execute(sqlGuid)
    if sqlGuid_rows is not None and len(sqlGuid_rows) > 0:
        sqlFinishUuid = (
                "select uuid from " + RepeatNameUuid1 + " where replace(name,',','') = " + getDbStr(
            escapeCharacter(name)) + ""
        )
        sqlUuid = (
                " SELECT uuid from " + RelationshipTmp2 +
                " where uuid in(" + sqlAllUuid + ") " +
                " and uuid not in(" + sqlFinishUuid + ")" +
                " and guid in(" + sqlGuid + ") " +
                " group by uuid "
        )
        sqlUuid_rows = client.execute(sqlUuid)
        if sqlUuid_rows is not None and len(sqlUuid_rows) > 0:
            sql1 = (
                    " insert into " + ExpertTmp3 +
                    " select " + ExpertQueryColumns + " from " + ExpertTmp2 +
                    " where uuid in(" + sqlUuid + ") "
            )
            client.execute(sql1)
            sql2 = (
                    " insert into " + RelationshipTmp3 +
                    " select uuid,guid from " + RelationshipTmp2 +
                    " where uuid in(" + sqlUuid + ") and guid in(" + sqlGuid + ") "
            )
            client.execute(sql2)


def repeatRelationship(client, name):
    sqlAllUuid = (
            " select uuid from " + ExpertTmp2 + " where replace(name,',','') = " + getDbStr(escapeCharacter(name))
    )
    sqlGuid = (
            " SELECT guid from " + RelationshipTmp2 + " where uuid in(" + sqlAllUuid + ") group by guid HAVING count() > 1 "
    )
    sqlGuid_rows = client.execute(sqlGuid)
    if sqlGuid_rows is not None and len(sqlGuid_rows) > 0:
        ExpertMap = {}
        for sqlGuid_rows_item in sqlGuid_rows:
            RelationshipMap = {}
            uuidArr = []
            guid = sqlGuid_rows_item[0]
            sqlItemUuid = (
                    " select uuid from " + RelationshipTmp2 +
                    " where guid = " + getDbStr(escapeCharacter(guid)) + " and uuid in(" + sqlAllUuid + ") "
            )
            sql1 = (
                    " select " + ExpertQueryColumns +
                    " from " + ExpertTmp2 + " where replace(name,',','') = " + getDbStr(escapeCharacter(name)) +
                    " and uuid in(" + sqlItemUuid + ")"
            )
            sql1_rows = client.execute(sql1)
            if sql1_rows is not None and len(sql1_rows) > 0:
                columns = ExpertQueryColumns.split(",")
                df = pd.DataFrame(sql1_rows, columns=columns)
                uuid = df.values[0][0]
                RelationshipMap[uuid] = []

                organizationArr = []
                Second_organizationArr = []
                emailArr = []
                countryArr = []
                organizationdeptArr = []
                keywordsArr = []
                subjectsArr = []
                journalsArr = []
                sql2 = (
                        " insert into " + RepeatNameUuid1 + "(name,uuid)VALUES"
                )
                for i in range(len(df)):
                    itemUuid = str(df.values[i][0])
                    sql2 += "(" + getDbStr(escapeCharacter(name)) + "," + getDbStr(escapeCharacter(itemUuid)) + "),"
                    uuidArr.append(itemUuid)
                    organizationArr = mergeBySemicolon(organizationArr, df.values[i][2])
                    Second_organizationArr = mergeBySemicolon(Second_organizationArr, df.values[i][3])
                    emailArr = mergeBySemicolon(emailArr, df.values[i][4])
                    countryArr = mergeBySemicolon(countryArr, df.values[i][7])
                    organizationdeptArr = mergeBySemicolon(organizationdeptArr, df.values[i][9])
                    keywordsArr = mergeBySemicolon(keywordsArr, df.values[i][10])
                    subjectsArr = mergeBySemicolon(subjectsArr, df.values[i][11])
                    journalsArr = mergeBySemicolon(journalsArr, df.values[i][12])
                if sql2.endswith(","):
                    sql2 = sql2[:len(sql2) - 1]
                    client.execute(sql2)

                organization = getMergeStrBySemicolon(organizationArr)
                Second_organization = getMergeStrBySemicolon(Second_organizationArr)
                email = getMergeStrBySemicolon(emailArr)
                reprintauthor = df.values[0][5]
                altname = df.values[0][6]
                country = getMergeStrBySemicolon(countryArr)
                firstauthor = df.values[0][8]
                organizationdept = getMergeStrBySemicolon(organizationdeptArr)
                keywords = getMergeStrBySemicolon(keywordsArr)
                subjects = getMergeStrBySemicolon(subjectsArr)
                journals = getMergeStrBySemicolon(journalsArr)
                source_email = df.values[0][13]
                itemExpert = [
                    uuid, name, organization, Second_organization, email, reprintauthor, altname, country, firstauthor,
                    organizationdept, keywords, subjects, journals, source_email
                ]
                if uuid not in ExpertMap.keys():
                    ExpertMap[uuid] = itemExpert
                else:
                    itemNewExpert = mergeRepeatExpert2(ExpertMap[uuid], itemExpert)
                    ExpertMap[uuid] = itemNewExpert

                if len(uuidArr) > 0:
                    uuidItemSql = ""
                    for itemUuid in uuidArr:
                        uuidItemSql += getDbStr(escapeCharacter(itemUuid)) + ","
                    if uuidItemSql.endswith(","):
                        uuidItemSql = uuidItemSql[:len(uuidItemSql) - 1]
                    sql3 = (
                            " select guid from " + RelationshipTmp2 +
                            " where uuid in(" + uuidItemSql + ") "
                    )
                    sql3_rows = client.execute(sql3)
                    if len(sql3_rows) > 0:
                        for i in range(len(sql3_rows)):
                            guidItem = sql3_rows[i][0]
                            oldGuidArr = RelationshipMap[uuid]
                            oldGuidArr.append(guidItem)
                            RelationshipMap[uuid] = oldGuidArr

                if len(RelationshipMap.keys()) > 0:
                    sql4 = (
                            " insert into " + RelationshipTmp3 + "(uuid,guid)VALUES"
                    )
                    guidArr = RelationshipMap[uuid]
                    guidArr = arrUnique(guidArr)
                    for guidItem in guidArr:
                        sql4 += "("
                        sql4 += getDbStr(escapeCharacter(uuid)) + "," + getDbStr(escapeCharacter(guidItem))
                        sql4 += "),"

                    if sql4.endswith(","):
                        sql4 = sql4[:len(sql4) - 1]
                        client.execute(sql4)

        if len(ExpertMap.keys()) > 0:
            for key in ExpertMap.keys():
                itemExpert = ExpertMap[key]
                sql5 = (
                        " insert into " + ExpertTmp3 + "(" + ExpertQueryColumns + ")VALUES(" +
                        getDbStr(escapeCharacter(itemExpert[0])) + "," +
                        getDbStr(escapeCharacter(itemExpert[1])) + "," +
                        getDbStr(escapeCharacter(itemExpert[2])) + "," +
                        getDbStr(escapeCharacter(itemExpert[3])) + "," +
                        getDbStr(escapeCharacter(itemExpert[4])) + "," +
                        getDbStr(escapeCharacter(itemExpert[5])) + "," +
                        getDbStr(escapeCharacter(itemExpert[6])) + "," +
                        getDbStr(escapeCharacter(itemExpert[7])) + "," +
                        getDbStr(escapeCharacter(itemExpert[8])) + "," +
                        getDbStr(escapeCharacter(itemExpert[9])) + "," +
                        getDbStr(escapeCharacter(itemExpert[10])) + "," +
                        getDbStr(escapeCharacter(itemExpert[11])) + "," +
                        getDbStr(escapeCharacter(itemExpert[12])) + "," +
                        getDbStr(escapeCharacter(itemExpert[13])) +
                        " ) "
                )
                client.execute(sql5)


def executeMergeRelationshipBySameName(client, name):
    print(name)
    # 重复GUID关系
    repeatRelationship(client, name)
    # 单一关系
    singleRelationship(client, name)


def doMergeRelationshipBySameNameThread(name):
    if name is not None and name != '':
        client = getClient()
        executeMergeRelationshipBySameName(client, name)


def initTable_step2_8_name(client):
    client.execute(ExpertTmp2Delete)
    client.execute(ExpertTmp2Create)
    client.execute(RelationshipTmp2Delete)
    client.execute(RelationshipTmp2Create)
    client.execute(RepeatNameDelete)
    client.execute(RepeatNameCreate)
    client.execute(RepeatNameUuidDelete)
    client.execute(RepeatNameUuidCreate)


def clearTable_step2_8_name(client):
    client.execute(RepeatNameDelete)
    client.execute(RepeatNameUuidDelete)


def clearTable_step2_8_org(client):
    client.execute(RepeatOrgDelete)
    client.execute(RepeatOrgUuidDelete)
    client.execute(RepeatOrgExecuteDelete)


def extractRepeatName(client):
    sql = (
            " insert into " + RepeatName +
            " select replace(name,',','') as name,count() as num from " + ExpertTmp1 +
            " group by name HAVING count() > 1 "
    )
    client.execute(sql)


def mergeBySameName(client):
    sql0_1 = (
            " SELECT name from " + RepeatName + " order by num desc "
    )
    sql0_1_rows = client.execute(sql0_1)
    if sql0_1_rows is not None and len(sql0_1_rows) > 0:
        index = 0
        while True:
            threadSize = len(threading.enumerate())
            if threadSize < constant.threadMaxSize:
                name = str(sql0_1_rows[index][0])
                t = threading.Thread(target=doMergeBySameNameThread, args=(name,))
                t.start()
                index += 1
                if index >= len(sql0_1_rows):
                    break
        while True:
            time.sleep(10)
            arr = threading.enumerate()
            print('threading alive count', len(arr))
            if len(arr) == 1:
                break


def extractSingleName(client):
    """
    提取单一的 name，直接保存
    """
    names = (
            " select name from " + RepeatName
    )
    sql1 = (
            " insert into " + ExpertTmp2 +
            " select " + ExpertQueryColumns + " from " + ExpertTmp1 +
            " where replace(name,',','') not in(" + names + ")"
    )
    client.execute(sql1)
    sql2 = (
            " insert into " + RelationshipTmp2 +
            " select uuid,guid from " + RelationshipTmp1 +
            " where uuid in (select uuid from " + ExpertTmp1 + " where replace(name,',','') not in(" + names + "))"
    )
    client.execute(sql2)


def initTable_step2_8_name2(client):
    client.execute(ExpertTmp3Delete)
    client.execute(ExpertTmp3Create)
    client.execute(RelationshipTmp3Delete)
    client.execute(RelationshipTmp3Create)
    client.execute(RepeatNameDelete)
    client.execute(RepeatNameCreate)
    client.execute(RepeatNameUuid1Delete)
    client.execute(RepeatNameUuid1Create)


def clearTable_step2_8_name2(client):
    client.execute(RepeatNameDelete)
    client.execute(RepeatNameUuid1Delete)


def extractRepeatName2(client):
    sql = (
            " insert into " + RepeatName +
            " select replace(name,',','') as name,count() as num from " + ExpertTmp2 +
            " group by name HAVING count() > 1 "
    )
    client.execute(sql)


def mergeRelationshipBySameName(client):
    sql0_1 = (
            " SELECT name from " + RepeatName + " order by num desc "
    )
    sql0_1_rows = client.execute(sql0_1)
    if sql0_1_rows is not None and len(sql0_1_rows) > 0:
        index = 0
        while True:
            threadSize = len(threading.enumerate())
            if threadSize < constant.threadMaxSize:
                name = str(sql0_1_rows[index][0])
                t = threading.Thread(target=doMergeRelationshipBySameNameThread, args=(name,))
                t.start()
                index += 1
                if index >= len(sql0_1_rows):
                    break
        while True:
            time.sleep(10)
            arr = threading.enumerate()
            print('threading alive count', len(arr))
            if len(arr) == 1:
                break


def extractSingleName2(client):
    """
    提取单一的 name，直接保存
    """
    names = (
            " select name from " + RepeatName
    )
    sql1 = (
            " insert into " + ExpertTmp3 +
            " select " + ExpertQueryColumns + " from " + ExpertTmp2 +
            " where replace(name,',','') not in(" + names + ")"
    )
    client.execute(sql1)
    sql2 = (
            " insert into " + RelationshipTmp3 +
            " select uuid,guid from " + RelationshipTmp2 +
            " where uuid in (select uuid from " + ExpertTmp2 + " where replace(name,',','') not in(" + names + "))"
    )
    client.execute(sql2)


def tmpDataIntoResultTable(client):
    client.execute(ExpertDelete)
    client.execute(ExpertCreate)
    client.execute(RelationshipDelete)
    client.execute(RelationshipCreate)

    sql1 = (
            " insert into " + Expert +
            " select " + ExpertQueryColumns + " from " + ExpertTmp3 +
            " group by " + ExpertQueryColumns
    )
    client.execute(sql1)
    sql2 = (
            " insert into " + Relationship +
            " select uuid,guid from " + RelationshipTmp3 +
            " group by uuid,guid "
    )
    client.execute(sql2)


def clearTmp(client):
    client.execute(ExpertTmpDelete)
    client.execute(ExpertTmp1Delete)
    client.execute(ExpertTmp2Delete)
    client.execute(ExpertTmp3Delete)

    client.execute(RelationshipTmpDelete)
    client.execute(RelationshipTmp1Delete)
    client.execute(RelationshipTmp2Delete)
    client.execute(RelationshipTmp3Delete)


def getClient():
    config = constant.dbConfig
    client = Client(host=config['host'], database=config['db'], user=config['user'], password=config['pwd'],
                    send_receive_timeout=config['timeout'])
    return client


def main():
    client = getClient()

    # 清除上一步骤的临时表
    clearTable_step2_8_org(client)

    # 初始化表
    initTable_step2_8_name(client)
    # 提取重复的 name
    extractRepeatName(client)
    # 合并 name 相同,organization 是子串的, Second_organization 是子串的
    mergeBySameName(client)
    # 提取单一的 name
    extractSingleName(client)
    # 清理临时表
    clearTable_step2_8_name(client)
    time.sleep(3)

    # 不同 source_email 得到的 name相同的作者，若关联的是同一篇论文，合并作者
    # 初始化表
    initTable_step2_8_name2(client)
    # 提取重复的 name
    extractRepeatName2(client)
    # 合并同一作者，重复 GUID 的关系
    mergeRelationshipBySameName(client)
    # 提取单一的 name
    extractSingleName2(client)
    # 清理临时表
    clearTable_step2_8_name2(client)

    # 从tmp3临时表，把数据导入结果表中
    tmpDataIntoResultTable(client)
    # 删除临时表tmp，tmp1，tmp2，tmp3
    clearTmp(client)


if __name__ == '__main__':
    main()
